#!/usr/bin/perl

#   Copyright 2007 Daniel J Shahin
#   This program is free software; you can redistribute it and/or modify
#   it under the terms of the GNU General Public License as published by
#   the Free Software Foundation; version 2 of the License.
# 
#   This program is distributed in the hope that it will be useful,
#   but WITHOUT ANY WARRANTY; without even the implied warranty of
#   MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
#   GNU General Public License for more details.

use lib qw( /home/dan/usr/local/lib/perl5/site_perl/5.8.7/
 /home/dan/usr/local/lib/perl5/site_perl/5.8.5/
		/home/dan/cpro.hijinxcomics.com/hijinx);
use DBI;
my $file = shift or die "must provide invoice filename";
my $db_file = shift or die "must provide DB filename";
my $dbh = DBI->connect( "dbi:SQLite:dbname=$db_file", "", "" ) or die "can't connect!" ;
my $sth = $dbh->prepare("insert into invoices values (NULL,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) ");
my $sth2 = $dbh->prepare("select sum(units_shipped) from invoices");
my $sth3 = $dbh->prepare("select count(distinct item_code)  from invoices");
my $sth4 = $dbh->prepare("select sum(units_shipped * retail_price) from invoices");
my $sth5 = $dbh->prepare("select invoice_id + 1 as new_invoice_id from invoices order by invoice_id desc limit 1");
$sth5->execute()|| die "can't get invoice id";
my ($invoice_id) = $sth5->fetchrow_array;
if ($invoice_id < 1){$invoice_id++};

$dbh->do("begin transaction ") or die "can't create transaction";
open (INVOICE, $file) || die "can't open $file";
my $linenum;
while (<INVOICE>){ #step through items array
        s/\r//;    #strip off carriage returns
        if ( $_ =~ /^\"/ ) {    #if line starts with a quote
            s/\"//g;
            push @headers, $_;
	    chomp @headers;
        }
        elsif ( $_ =~ /^\d/ || $_ =~ /^\-/ ) {
	    $linenum++;
            s/\"//g;
            my (
                $qty,          $itemno,   $descr,  $retail_price,
                $unit_price,   $inv_amt,  $cat_id, $order_type,
                $processed_as, $order_no, $upc,    $isbn,
                $ean,          $po_no,    $allocated
              )
              = split(/,/);
            my $date = $headers[3];
            chomp $date;
            $date =~ s/(\d\d)\/(\d\d)\/(\d\d)/20$3-$1-$2/;
            my $account_num = $headers[1];
            $discount =~ s/ //g;
            chop $itemno;
            my $discount = chop $itemno;
	    $sth->execute($invoice_id, $qty,          $itemno,   $descr,  $retail_price,
			$unit_price,   $inv_amt,  $cat_id, $order_type,
			$processed_as, $order_no, $upc,    $isbn,
			$ean,          $po_no,    $allocated, $account_num, $date) || die "can't insert: " . $sth->errstr;
	print "$date $itemno $linenum $account_num invoice:$invoice_id\n";
	}
}
$dbh->do("commit transaction ") or die "can't commit";
$sth2->execute();
$sth3->execute();
$sth4->execute();
my $count = $sth2->fetchrow_array;
my $lcount = $sth3->fetchrow_array;
my $retail2 = $sth4->fetchrow_array;
print "piece count: $count\n";
print "line count: $lcount\n";
print "warehouse: ", $ref->{'warehouse'}, "\n";
print "account: ", $ref->{'account-number'}, "\n";
print "date: ", $ref->{'date'}, "\n";
print "total cost: $total\n";
print "total retail db: $retail2\n";
